﻿using EFFC.Frame.Net.Module.Extend.EWRA.Attributes;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using EFFC.Extends.LinqDLR2SQL;
using EFFC.Frame.Net.Unit.DB;
using EFFC.Frame.Net.Base.Data.Base;
using EFFC.Frame.Net.Module.Extend.EWRA.Attributes.Validation;
using EFFC.Frame.Net.Base.Common;
using EFFC.Frame.Net.Base.Data;
using EFFC.Frame.Net.Module.Business.Datas;
using ICPFrameLib.Business;
using ICPFrameLib.Attributes;
using EFFC.Frame.Net.Unit.DB.Parameters;
using ICPFrameLib.Common;
using System.IO;

namespace XHRPMIS.Business.v1._0
{
    public class ICPDataSource: MyRestLogic
    {
        [EWRAVisible(true)]
        [EWRARoute("get", "/data_source/category")]
        [EWRARouteDesc("获取启用的数据源列表")]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:[{
code:'代码',
text:'名称'
}]
}")]
        object GetSourceCategoryList()
        {
            SetCacheEnable(false);
            var metaup = DB.NewMetaUnitParameter();
            return new
            {
                code = "success",
                msg = "",
                data = (from t in DB.LamdaTable(metaup, "EXTEND_DATASOURCE_CATEGORY", "a")
                        select new
                        {
                            code = t.Code,
                            text = t.Text
                        }).GetQueryList(metaup).ToList()
            };
        }
        [EWRARoute("get", "/data_source/activelist")]
        [EWRARouteDesc("获取启用的数据源列表")]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:[{
SourceUID:'资料PK',
SourceName:'数据来源名称',
SourceCategory:'数据来源类型',
SourceCategory_text:'数据来源类型text',
ConnectStr:'连接字符串',
IsActive:'是否启用'
}]
}")]
        object GetActiveList()
        {
            SetCacheEnable(false);
            var metaup = DB.NewMetaUnitParameter();
            return new
            {
                code = "success",
                msg = "",
                data = DataSourceUtilities.GetAllSources(metaup).Where(w => w.IsActive).ToArray()
            };
        }
        [EWRAAuth(true)]
        [EWRARoute("get", "/data_source/{id}/allobjects")]
        [EWRARouteDesc("获取指定数据源下的所有table/view对象列表")]
        [EWRAAddInput("id", "string", "数据源ID或名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:{
    table_list:[{table_name:'表名称'}],
    view_list:[{view_name:'视图名称'}],
}
}")]
        object GetAllObjects(string id)
        {
            SetCacheEnable(false);
            var metaup = DB.NewMetaUnitParameter();
            var s = DataSourceUtilities.GetSource(metaup, id);
            if (s == null)
            {
                return new
                {
                    code = "failed",
                    msg = "无该数据源信息"
                };
            }
            if (!new string[] { "SqlServer", "Sqlite", "MySql", "Oracle" }.Contains(s.SourceCategory))
            {
                return new
                {
                    code = "failed",
                    msg = $"本接口不支持{s.SourceCategory}类型"
                };
            }
            var msg = "";
            if(!IsValidConnstr(s.SourceCategory,s.ConnectStr,ref msg))
            {
                return new
                {
                    code = "failed",
                    msg
                };
            }
            var table_list = s.GetTables(this).Select(d =>
            {
                return new
                {
                    table_name = d.Name
                };
            }).ToArray();
            var view_list = s.GetViews(this).Select(d =>
            {
                return new
                {
                    view_name = d.Name
                };
            }).ToArray();
            return new
            {
                code = "success",
                msg = "",
                data = new
                {
                    table_list,
                    view_list
                }
            };
        }
        [EWRAAuth(true)]
        [EWRARoute("get", "/data_source/{id}/table/{name}/schema")]
        [EWRARouteDesc("获取指定table的Schema")]
        [EWRAAddInput("id", "string", "数据源ID或名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAAddInput("name", "string", "table名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:[{
    column_name:'栏位名称',
    column_type:'栏位类型',
    is_nullable:'是否允许为空',
    length:'栏位长度',
    precision:'栏位精度',
    scale:'栏位比列'
}]
}")]
        object GetTableSchema(string id, string name)
        {
            SetCacheEnable(false);
            var metaup = DB.NewMetaUnitParameter();
            var s = DataSourceUtilities.GetSource(metaup, id);
            if (s == null)
            {
                return new
                {
                    code = "failed",
                    msg = "无该数据源信息"
                };
            }
            if (!new string[] { "SqlServer", "Sqlite", "MySql", "Oracle" }.Contains(s.SourceCategory))
            {
                return new
                {
                    code = "failed",
                    msg = $"本接口不支持{s.SourceCategory}类型"
                };
            }
            var table = s.GetTables(this)[name];
            if (table == null)
            {
                return new
                {
                    code = "failed",
                    msg = $"无{name}的表对象"
                };
            }
            return new
            {
                code = "success",
                msg = "",
                data = table.GetSchema(this).Select(d =>
                {
                    return new
                    {
                        column_name = d.Column_Name,
                        column_type = d.Column_Type,
                        is_nullable = d.IsNullable,
                        length = d.Length,
                        precision = d.Precision,
                        scale = d.Scale
                    };
                }).ToArray()
            };
        }
        [EWRAAuth(true)]
        [EWRARoute("get", "/data_source/{id}/view/{name}/schema")]
        [EWRARouteDesc("获取指定table的Schema")]
        [EWRAAddInput("id", "string", "数据源ID或名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAAddInput("name", "string", "view名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:[{
    column_name:'栏位名称',
    column_type:'栏位类型',
    is_nullable:'是否允许为空',
    length:'栏位长度',
    precision:'栏位精度',
    scale:'栏位比列'
}]
}")]
        object GetViewSchema(string id, string name)
        {
            SetCacheEnable(false);
            var metaup = DB.NewMetaUnitParameter();
            var s = DataSourceUtilities.GetSource(metaup, id);
            if (s == null)
            {
                return new
                {
                    code = "failed",
                    msg = "无该数据源信息"
                };
            }
            if (!new string[] { "SqlServer", "Sqlite", "MySql", "Oracle" }.Contains(s.SourceCategory))
            {
                return new
                {
                    code = "failed",
                    msg = $"本接口不支持{s.SourceCategory}类型"
                };
            }
            var view = s.GetViews(this)[name];
            if (view == null)
            {
                return new
                {
                    code = "failed",
                    msg = $"无{name}的视图对象"
                };
            }
            return new
            {
                code = "success",
                msg = "",
                data = view.GetSchema(this).Select(d =>
                {
                    return new
                    {
                        column_name = d.Column_Name,
                        column_type = d.Column_Type,
                        is_nullable = d.IsNullable,
                        length = d.Length,
                        precision = d.Precision,
                        scale = d.Scale
                    };
                }).ToArray()
            };
        }
        [ActionAuth("DataSource")]
        [EWRARouteDesc("获取所有数据源列表")]
        [EWRAOutputDesc("返回结果", @"{
result:[{
SourceUID:'资料PK',
SourceName:'数据来源名称',
SourceCategory:'数据来源类型',
SourceCategory_text:'数据来源类型text',
ConnectStr:'连接字符串',
IsActive:'是否启用'
}]
}")]
        public override List<object> get()
        {
            SetCacheEnable(false);
            var metaup = DB.NewMetaUnitParameter();
            return DataSourceUtilities.GetAllSources(metaup).ToList<object>();
        }
        [ActionAuth("DataSource")]
        [EWRAEmptyValid("source_name,source_category,connect_str")]
        [EWRARouteDesc("新增一个默认启用的数据源")]
        [EWRAAddInput("source_name", "string", "数据源名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("source_category", "string", "数据源类型", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("connect_str", "string", "连接串", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息"",
uid:""成功时候返回的UID""
}")]
        public override object post()
        {
            var source_name = ComFunc.nvl(PostDataD.source_name);
            var source_category = ComFunc.nvl(PostDataD.source_category);
            var connect_str = ComFunc.nvl(PostDataD.connect_str);

            var metaup = DB.NewMetaUnitParameter();
            BeginTrans();
            if ((from t in DB.LamdaTable(metaup, "EXTEND_DATASOURCE", "a")
                 where t.SourceName == source_name
                 select t).IsExists(metaup))
            {
                return new
                {
                    code = "failed",
                    msg = "已存在同名数据源"
                };
            }
            var uid = Guid.NewGuid().ToString();
            DB.QuickInsert(metaup, "EXTEND_DATASOURCE", new
            {
                SourceUID = uid,
                SourceName = source_name,
                SourceCategory = source_category,
                ConnectStr = connect_str,
                IsActive = 1,
                add_id = TokenPayLoad.ID,
                add_name = ComFunc.nvl(TokenPayLoad["name"]),
                add_ip = ClientInfo.IP,
                add_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            });
            CommitTrans();
            DataSourceUtilities.RefreshInfo(metaup);

            return new
            {
                code = "success",
                msg = "操作成功",
                uid
            };
        }
        [ActionAuth("DataSource")]
        [EWRAEmptyValid("source_name,source_category,connect_str")]
        [EWRARouteDesc("修改指定的数据源")]
        [EWRAAddInput("id", "string", "资料pk", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAAddInput("source_name", "string", "数据源名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("source_category", "string", "数据源类型", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("connect_str", "string", "连接串", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息""
}")]
        public override object patch(string id)
        {
            var source_name = ComFunc.nvl(PostDataD.source_name);
            var source_category = ComFunc.nvl(PostDataD.source_category);
            var connect_str = ComFunc.nvl(PostDataD.connect_str);

            var metaup = DB.NewMetaUnitParameter();
            BeginTrans();
            dynamic info = (from t in DB.LamdaTable(metaup, "EXTEND_DATASOURCE", "a")
                            where t.SourceUID == id
                            select t).GetQueryList(metaup).FirstOrDefault();
            if (info == null)
            {
                return new
                {
                    code = "failed",
                    msg = "无该资料"
                };
            }
            if ((from t in DB.LamdaTable(metaup, "EXTEND_DATASOURCE", "a")
                 where t.SourceUID != id && t.SourceName == source_name
                 select t).IsExists(metaup))
            {
                return new
                {
                    code = "failed",
                    msg = "已存在同名数据源"
                };
            }
            DB.QuickUpdate(metaup, "EXTEND_DATASOURCE", new
            {
                SourceName = source_name,
                SourceCategory = source_category,
                ConnectStr = connect_str,
                last_id = TokenPayLoad.ID,
                last_name = ComFunc.nvl(TokenPayLoad["name"]),
                last_ip = ClientInfo.IP,
                last_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }, new
            {
                info.SourceUID
            });
            DataSourceUtilities.RefreshInfo(metaup);
            CommitTrans();

            return new
            {
                code = "success",
                msg = "操作成功"
            };
        }
        [ActionAuth("DataSource")]
        [EWRARouteDesc("删除一个数据源")]
        [EWRAAddInput("id", "string", "UID", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAOutputDesc("返回结果", @"根据http的状态码来识别，204标识操作成功，404表示操作识别未找到删除的资料")]
        public override bool delete(string id)
        {
            var metaup = DB.NewMetaUnitParameter();
            DB.QuickDelete(metaup, "EXTEND_DATASOURCE", new
            {
                SourceUID = id
            });
            DataSourceUtilities.RefreshInfo(metaup);
            return true;
        }
        [ActionAuth("DataSource")]
        [EWRARoute("patch", "/icpdatasource/{id}/active")]
        [EWRARouteDesc("启用指定的数据源")]
        [EWRAAddInput("id", "string", "资料pk", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息""
}")]
        public object Active(string id)
        {
            var metaup = DB.NewMetaUnitParameter();
            BeginTrans();
            dynamic info = (from t in DB.LamdaTable(metaup, "EXTEND_DATASOURCE", "a")
                            where t.SourceUID == id
                            select t).GetQueryList(metaup).FirstOrDefault();
            if (info == null)
            {
                return new
                {
                    code = "failed",
                    msg = "无该资料"
                };
            }
            if (BoolStd.IsNotBoolThen(info.IsActive))
            {
                return new
                {
                    code = "failed",
                    msg = "该数据源已启用"
                };
            }
            DB.QuickUpdate(metaup, "EXTEND_DATASOURCE", new
            {
                IsActive = 1,
                last_id = TokenPayLoad.ID,
                last_name = ComFunc.nvl(TokenPayLoad["name"]),
                last_ip = ClientInfo.IP,
                last_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }, new
            {
                info.SourceUID
            });
            DataSourceUtilities.RefreshInfo(metaup);
            CommitTrans();

            return new
            {
                code = "success",
                msg = "操作成功"
            };
        }
        [ActionAuth("DataSource")]
        [EWRARoute("patch", "/icpdatasource/{id}/deactive")]
        [EWRARouteDesc("停用指定的数据源")]
        [EWRAAddInput("id", "string", "资料pk", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息""
}")]
        public object Deactive(string id)
        {
            var metaup = DB.NewMetaUnitParameter();
            BeginTrans();
            dynamic info = (from t in DB.LamdaTable(metaup, "EXTEND_DATASOURCE", "a")
                            where t.SourceUID == id
                            select t).GetQueryList(metaup).FirstOrDefault();
            if (info == null)
            {
                return new
                {
                    code = "failed",
                    msg = "无该资料"
                };
            }
            if (!BoolStd.IsNotBoolThen(info.IsActive))
            {
                return new
                {
                    code = "failed",
                    msg = "该数据源已停用"
                };
            }
            DB.QuickUpdate(metaup, "EXTEND_DATASOURCE", new
            {
                IsActive = 0,
                last_id = TokenPayLoad.ID,
                last_name = ComFunc.nvl(TokenPayLoad["name"]),
                last_ip = ClientInfo.IP,
                last_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }, new
            {
                info.SourceUID
            });
            DataSourceUtilities.RefreshInfo(metaup);
            CommitTrans();

            return new
            {
                code = "success",
                msg = "操作成功"
            };
        }

        [EWRARoute("post", "/icpdatasource/builder_connectstr")]
        [EWRARouteDesc("根据连接参数生成连接串")]
        [EWRAEmptyValid("category")]
        [EWRAAddInput("category", "string", "连接类型:SqlServer,Oracle,MySql,Sqlite", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("server_addr", "string", "目标服务器地址或IP", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("server_port", "int", "目标服务器访问端口号", "根据不同类型使用不用的默认端口号", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("db_name", "string", "目标数据库名称", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("id", "string", "访问账号", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("pw", "string", "访问密码", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("sqlite_file", "File", "Sqlite的文件，当category为Sqlite时必须上传，文件扩展名为.db", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息"",
data:'连接串'
}")]
        public object BuildConnectStr()
        {
            string category = ComFunc.nvl(PostDataD.category);
            string server_addr = ComFunc.nvl(PostDataD.server_addr);
            string server_port = ComFunc.nvl(PostDataD.server_port);
            string db_name = ComFunc.nvl(PostDataD.db_name);
            string id = ComFunc.nvl(PostDataD.id);
            string pw = ComFunc.nvl(PostDataD.pw);
            FrameUploadFile file = GetUploadFile("sqlite_file");
            

            if(!new string[] { "SqlServer", "Oracle", "MySql","Sqlite" }.Contains(category))
            {
                return new
                {
                    code = "failed",
                    msg = $"不支持{category}类型"
                };
            }
            if (category.ToLower() == "sqlite")
            {
                if (file == null)
                {
                    return new
                    {
                        code = "failed",
                        msg = "请上传Sqlite的数据文件"
                    };
                }
                if(Path.GetExtension(file.FileName).Replace(".","").ToLower() != "db")
                {
                    return new
                    {
                        code = "failed",
                        msg = "Sqlite的数据文件格式不正确"
                    };
                }
            }
            else
            {
                if (server_addr == "" || db_name == "" || id == "" || pw == "")
                {
                    return new
                    {
                        code = "failed",
                        msg = "参数不许为空"
                    };
                }
            }
            var data = "";
            var port = 0;
            switch(category.ToLower())
            {
                case "sqlserver":
                    port = IntStd.IsNotIntThen(server_port, 1433);
                    data = $"Password={pw};Persist Security Info=True;User ID={id};Initial Catalog={db_name};Data Source={server_addr}:{port};pooling=true;connection lifetime=30;min pool size = 1;max pool size=2000";
                    break;
                case "mysql":
                    port = IntStd.IsNotIntThen(server_port, 3306);
                    data = $"Database={db_name};Data Source={server_addr};Port={port};User Id={id};Password={pw};Charset=utf8;TreatTinyAsBoolean=false;Convert Zero Datetime=true;Allow Zero Datetime=true;ConnectionLifeTime=30;MaximumPoolSize=2000";
                    break;
                case "oracle":
                    port = IntStd.IsNotIntThen(server_port, 1521);
                    data = $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={server_addr})(PORT={port})))(CONNECT_DATA=(SERVER=DEDICATED)(SID={db_name})));User Id={id};Password={pw};";
                    break;
                case "sqlite":
                    var name = $"SqliteFile_{DateTime.Now.ToString("yyyyMMddHHmmss")}.db";
                    file.SaveAs(Path.GetFullPath($"AppData/{name}"));
                    data = $"Data Source=./AppData/{name}";
                    break;
                default:
                    data = "";
                    break;
            }
            return new
            {
                code = "success",
                msg = "",
                data
            };
        }
        [EWRARoute("post", "/icpdatasource/test_connectstr")]
        [EWRARouteDesc("测试连接串是否有效")]
        [EWRAEmptyValid("category,connstr")]
        [EWRAAddInput("category", "string", "连接类型:SqlServer,Oracle,MySql", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAAddInput("connstr", "string", "连接串", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息""
}")]
        public object TestConnectStr()
        {
            string category = ComFunc.nvl(PostDataD.category);
            string connstr = ComFunc.nvl(PostDataD.connstr);
            var msg = "";

            if (IsValidConnstr(category,connstr,ref msg))
            {
                return new
                {
                    code = "success",
                    msg = "连接成功"
                };
            }
            else
            {
                return new
                {
                    code = "failed",
                    msg = $"连接失败:{msg}"
                };
            }
        }

        private bool IsValidConnstr(string category, string connstr,ref string msg)
        {
            if (!new string[] { "SqlServer", "Oracle", "MySql", "Sqlite" }.Contains(category))
            {
                msg = $"不支持{category}类型";
                return false;
            }
            try
            {
                var up = DB.NewCustomerDBUnitParameter(category, connstr);
                return true;
            }catch(Exception ex)
            {
                msg = ex.Message;
                return false;
            }
        }
    }
}
